using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using SMS.OnLine.DataAccessLayer;
using SMS.OnLine.BusinessLogicLayer;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using SMS.OnLine.Security;
using System.Collections;

/// <summary>
/// Summary description for SQLDataAccessLayer
/// </summary>
namespace SMS.OnLine.DataAccessLayer
{
    public class SMSSQLDataAccess : SMSDataAccess
    {
        public override MessageEntry GetMessageEntryById(string msgId)
        {
            MessageEntry messageEntry = new MessageEntry(string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, DateTime.Now, string.Empty);
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_get_msg_by_id";
                    cmd.Parameters.Add("@msg_id", SqlDbType.VarChar).Value = msgId;

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            messageEntry = new MessageEntry(msgId,
                                (string)reader["caption"],
                                (string)reader["category"],
                                (string)reader["content"],
                                (string)reader["sender"],
                                (string)reader["sender_dept"],
                                (string)reader["rcv_area_list"],
                                (string)reader["rcv_store_list"],
                                (DateTime)reader["edit_date"],
                                (string)reader["status"]);
                        }
                    }
                }
            }
            return messageEntry;
        }

        public override List<MessageEntry> GetTop10MessageEntries()
        {
            List<MessageEntry> messageEntryList = new List<MessageEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_get_top10_message";

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MessageEntry messageEntry = new MessageEntry((string)reader["msg_id"],
                                (string)reader["caption"],
                                (string)reader["category"],
                                (string)reader["content"],
                                (string)reader["sender"],
                                (string)reader["sender_dept"],
                                (string)reader["rcv_area_list"],
                                (string)reader["rcv_store_list"],
                                (DateTime)reader["edit_date"],
                                (string)reader["status"]);
                            messageEntryList.Add(messageEntry);
                        }
                    }
                }
            }
            return messageEntryList;
        }

        public override List<MessageEntry> GetMessageByConditionEntries(string category, string senderDept, DateTime beginDate, DateTime endDate, string caption, string status)
        {
            List<MessageEntry> messageEntryList = new List<MessageEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_get_message";

                    cmd.Parameters.Add("@category", SqlDbType.NVarChar).Value = category;
                    cmd.Parameters.Add("@sender_dept", SqlDbType.NVarChar).Value = senderDept;
                    cmd.Parameters.Add("@begin_date", SqlDbType.DateTime).Value = beginDate.ToShortDateString();
                    cmd.Parameters.Add("@end_date", SqlDbType.DateTime).Value = endDate.ToShortDateString();
                    cmd.Parameters.Add("@caption", SqlDbType.NVarChar).Value = (String.IsNullOrEmpty(caption)) ? "" : caption;
                    cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = status;
                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MessageEntry messageEntry = new MessageEntry((string)reader["msg_id"],
                                (string)reader["caption"],
                                (string)reader["category"],
                                (string)reader["content"],
                                (string)reader["sender"],
                                (string)reader["sender_dept"],
                                (string)reader["rcv_area_list"],
                                (string)reader["rcv_store_list"],
                                (DateTime)reader["edit_date"],
                                (string)reader["status"]);
                            messageEntryList.Add(messageEntry);
                        }
                    }
                }
            }
            return messageEntryList;
        }

        public override List<MessageCategoryEntry> GetMessageCategoryEntries()
        {
            List<MessageCategoryEntry> categoryEntries = new List<MessageCategoryEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_get_msg_category";

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MessageCategoryEntry categoryEntry = new MessageCategoryEntry((string)reader["category"]);
                            categoryEntries.Add(categoryEntry);
                        }
                    }
                }
            }
            return categoryEntries;
        }

        public override List<MessageStatusEntry> GetMessageStatusEntries()
        {
            List<MessageStatusEntry> entries = new List<MessageStatusEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_get_msg_status";

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MessageStatusEntry entry = new MessageStatusEntry((string)reader["status"]);
                            entries.Add(entry);
                        }
                    }
                }
            }
            return entries;
        }

        public override List<DepartmentEntry> GetMessageDepartmentEntries(string department)
        {
            List<DepartmentEntry> entries = new List<DepartmentEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_get_msg_dept";
                    cmd.Parameters.Add("@department", SqlDbType.NVarChar).Value = department;

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            DepartmentEntry entry = new DepartmentEntry((string)reader["sender_dept"]);
                            entries.Add(entry);
                        }
                    }
                }
            }
            return entries;
        }

        public override bool SaveMessage(bool isNew, MessageEntry message)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_save_message";
                    cmd.Parameters.Add("@is_new", SqlDbType.Bit).Value = (isNew ? 1 : 0);
                    cmd.Parameters.Add("@msg_id", SqlDbType.VarChar).Value = message.MsgId;
                    cmd.Parameters.Add("@caption", SqlDbType.NVarChar).Value = message.Caption;
                    cmd.Parameters.Add("@category", SqlDbType.NVarChar).Value = message.Category;
                    cmd.Parameters.Add("@content", SqlDbType.NVarChar).Value = message.Content;
                    cmd.Parameters.Add("@sender", SqlDbType.NVarChar).Value = message.Sender;
                    cmd.Parameters.Add("@sender_dept", SqlDbType.NVarChar).Value = message.SenderDepartment;
                    cmd.Parameters.Add("@rcv_area_list", SqlDbType.NVarChar).Value = message.ReceiveAreaList;
                    cmd.Parameters.Add("@rcv_store_list", SqlDbType.NVarChar).Value = message.ReceiveStoreList;
                    cmd.Parameters.Add("@edit_date", SqlDbType.DateTime).Value = message.EditDate;
                    cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = message.Status;
                    conn.Open();
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }

        }

        public override Passport Login(string userId, string password)
        {
            Passport passport = new Passport();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_user_login";
                    cmd.Parameters.Add("@user_id", SqlDbType.NVarChar).Value = userId;
                    cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;
                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            SMSIdentity identity = new SMSIdentity();
                            identity.Id = ((string)reader["user_id"]);
                            identity.Name = ((string)reader["user_name"]);
                            identity.Gender = ((string)reader["gender"]);
                            identity.Company = ((string)reader["company"]);
                            identity.Area = ((string)reader["area"]);
                            identity.Department = ((string)reader["department"]);
                            identity.Title = ((string)reader["title"]);
                            identity.Email = ((string)reader["email"]);
                            identity.Tel = ((string)reader["tel"]);
                            identity.Mobile = ((string)reader["mobile"]);
                            identity.IsApproved = ((bool)reader["is_approved"]);
                            identity.LastLoginDate = ((DateTime)reader["last_login_date"]);
                            identity.Status = ((string)reader["status"]);
                            passport.Identity = identity;
                        }
                    }
                    //if (passport.Identity.IsAuthenticated)
                    //{
                    //    cmd.Parameters.Clear();
                    //    cmd.CommandText = "sp_get_user_roles";
                    //    cmd.Parameters.Add("@user_id", SqlDbType.VarChar).Value = ((SMSIdentity)(passport.Identity)).Id;
                    //    using (SqlDataReader reader = cmd.ExecuteReader())
                    //    {
                    //        ArrayList roles = new ArrayList();
                    //        while (reader.Read())
                    //        {
                    //            roles.Add((string)reader["role_name"]);
                    //        }
                    //        passport.Roles = roles;
                    //    }
                    //}
                }
            }
            return passport;
        }
    }

    public class DataMartSQLDataAccess : DataMartDataAccess
    {
        public override List<AreaEntry> GetAllAreaEntries()
        {
            List<AreaEntry> entries = new List<AreaEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select distinct CRegionName from dbo.Dim_StoreInfo order by CRegionName";

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AreaEntry entry = new AreaEntry((string)reader["cregionname"]);
                            entries.Add(entry);
                        }
                    }
                }
            }
            return entries;
        }

        public override List<StoreEntry> GetAllStoreEntries()
        {
            List<StoreEntry> entries = new List<StoreEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select StoreID, CStoreName, CRegionName from dbo.Dim_StoreInfo order by CRegionName, StoreID";

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            StoreEntry entry = new StoreEntry((string)reader["storeid"], (string)reader["cstorename"], (string)reader["cregionname"]);
                            entries.Add(entry);
                        }
                    }
                }
            }
            return entries;
        }

        public override List<StoreEntry> GetAllStoreByAreaEntries(string area)
        {
            List<StoreEntry> entries = new List<StoreEntry>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select StoreID, CStoreName,CRegionName from dbo.Dim_StoreInfo where CRegionName =@area order by CRegionName, StoreID";
                    cmd.Parameters.Add("@area", SqlDbType.NVarChar).Value = area;

                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            StoreEntry entry = new StoreEntry((string)reader["storeid"], (string)reader["cstorename"], (string)reader["cregionname"]);
                            entries.Add(entry);
                        }
                    }
                }
            }
            return entries;
        }
    }


}